package com.gery.tn.backend.service.jdbc;

import com.gery.tn.backend.model.Client;
import com.gery.tn.backend.service.ClientService;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;



public class JDBCClientService implements ClientService {

    private Connection connection;
    private Statement statement;
    private ResultSet resultSet;
    private PreparedStatement preparedStatement;
    
        
   
    public JDBCClientService() {
        try{
            connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/tianails", "root", "");
            statement = connection.createStatement();
        } catch (SQLException e){
            System.out.println(e.getMessage());
        }
    }
   
    @Override
    public void saveClient(Client client) {
        try {
            if (client.getID() == 0) {
                saveWithPrepStatement(client);
            } else {
                statement.execute("UPDATE clients SET name='" + client.getName() + "',tel='" + client.getTel() + 
                                                    "',mail='" + client.getEmail() + "',webid='" + client.getWebID() + 
                                                    "',fbid='" + client.getFbID() + "',points=" + client.getPoints() + 
                                  " WHERE id=" + client.getID());
                statement.execute("UPDATE appointments SET clientname='" + client.getName() +"' WHERE clientid=" + client.getID());
            }
        } catch (SQLException ex) {
            Logger.getLogger(JDBCClientService.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    @Override
    public void deleteClient(long id) {
        try {
            statement.execute("DELETE FROM clients WHERE id=" + id);
            statement.execute("DELETE FROM appointments WHERE clientid=" + id);
        } catch (SQLException ex) {
            Logger.getLogger(JDBCClientService.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    
    @Override
    public Client getClient(long id) {
        try {
            resultSet = statement.executeQuery("SELECT id, name, tel, mail, webid, fbid, points FROM clients WHERE id=" + id);
            resultSet.next();
            String nam = resultSet.getString("name");
            String tel = resultSet.getString("tel");
            String mail = resultSet.getString("mail");
            String webid = resultSet.getString("webid");
            String fbid = resultSet.getString("fbid");
            int points = resultSet.getInt("points");
            return new Client(id, nam, tel, mail, points, webid, fbid);
        } catch (SQLException ex) {
            Logger.getLogger(JDBCClientService.class.getName()).log(Level.SEVERE, null, ex);
            return null;
        }
    }
    
    @Override
    public List<Client> getAllClients() {
        List<Client> temp = new ArrayList<>();
        try {
            resultSet = statement.executeQuery("SELECT id, name FROM clients");
            while (resultSet.next()) {
                Client client = new Client();
                client.setID(resultSet.getLong("id"));
                client.setName(resultSet.getString("name"));
                temp.add(client);
            }
        } catch (SQLException ex) {
            Logger.getLogger(JDBCClientService.class.getName()).log(Level.SEVERE, null, ex);
        }
        return temp;
    }

    @Override
    public List<Client> searchFor(String str) {
        List<Client> names = getAllClients();
        List<Client> temp = new ArrayList<>(10);
        int count = 0;
        try {
            int clientNr = names.size();
            for (int i=0; i<clientNr; i++) {
                if (count == 10) {
                    return temp;
                }
                if (names.get(i).getName().toLowerCase().contains(str)) {
                    count++;
                    resultSet = statement.executeQuery("SELECT id, name, tel, points FROM clients WHERE id=" + names.get(i).getID());
                    resultSet.next();
                    long id = resultSet.getLong("id");
                    String name = resultSet.getString("name");
                    String tel = resultSet.getString("tel");
                    int points = resultSet.getInt("points");
                    temp.add(new Client(id, name, tel, "", points, "", ""));
                }
            }
        } catch (SQLException ex) {
            Logger.getLogger(JDBCClientService.class.getName()).log(Level.SEVERE, null, ex);
        }
        return temp;
    }
    
    
    public void saveWithPrepStatement(Client client) throws SQLException {
        preparedStatement = connection.prepareStatement("INSERT INTO clients (name, tel, mail, webid, fbid, points) VALUES (?, ?, ?, ?, ?, ?)");
        preparedStatement.setString(1, client.getName());
        preparedStatement.setString(2, client.getTel());
        preparedStatement.setString(3, client.getEmail());
        preparedStatement.setString(4, client.getWebID());
        preparedStatement.setString(5, client.getFbID());
        preparedStatement.setInt(6, client.getPoints());
        preparedStatement.executeUpdate();
    }
    
}
